package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.LongStream;

@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private WorkspaceService workspaceService;

    /**
     * 营业额统计
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return
     */
    @Override
    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
//        ['2023-09-01','2023-09-02'......]
//        [316.00,123.00，0.0.....]

        //1、获取起始日期之间的日期集合
        List<LocalDate> dateList = this.getDateList(begin,end);

        //2、根据日期去查询营业额
        //统计方式一：按天统计（会发起多起查询，效率不高）
        /*List<Double> amountList = new ArrayList<>(dateList.size());
        for (LocalDate date : dateList) {
            Map paramsMap = new HashMap();
            paramsMap.put("date",date);
            paramsMap.put("status", Orders.COMPLETED);
            Double amount = this.orderMapper.sumAmountByParams(paramsMap);
            amount = ObjectUtil.isEmpty(amount) ? 0.0:amount;
            amountList.add(amount);
        }*/

        //统计方式二：按日期分组进行统计（只用发起一次查询，效率高，推荐使用）
        Map paramsMap = new HashMap();
        paramsMap.put("beginDate",begin);
        paramsMap.put("endDate",end);
        paramsMap.put("status", Orders.COMPLETED);
        List<Map> turnoverMapList = this.orderMapper.sumAmountByparamsWithGroupby(paramsMap);
        Map<String, BigDecimal> turnoverMap = turnoverMapList.stream().collect(Collectors.toMap(t -> (String) t.get("date"), t -> (BigDecimal) t.get("amount")));
        List<BigDecimal> amountList = new ArrayList<>(dateList.size());
        for (LocalDate date : dateList) {
            BigDecimal amount = turnoverMap.getOrDefault(date.toString(),BigDecimal.valueOf(0.0));
            amountList.add(amount);
        }

        //3、拼接出参的两个字符串，封装vo返回
        String dateListStr = StringUtils.join(dateList,",");
        dateListStr = dateList.stream().map(date -> date.toString()).collect(Collectors.joining(","));
        String turnoverListStr = amountList.stream().map(a -> a.toString()).collect(Collectors.joining(","));

        return TurnoverReportVO.builder().dateList(dateListStr).turnoverList(turnoverListStr).build();
    }

    /**
     * 用户统计
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return
     */
    @Override
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        //        ['2023-09-01','2023-09-02'......]
        //        [31,12]
        //        [31,43]

        //1、获取起始日期之间的日期集合
        List<LocalDate> dateList = this.getDateList(begin, end);

        //2、根据日期获取新增用户数以及 截止当天的用户总数
        List<Integer> newUserList = new ArrayList<>();
        List<Integer> totalUserList = new ArrayList<>();
        //方式一：按天统计（每天新增用户数、按日期分组进行统计）
        /*for (LocalDate date : dateList) {
            Map paramsMap = new HashMap();
            paramsMap.put("date",date);
            paramsMap.put("newUserFlag",true);
            //SELECT COUNT(*) FROM `user` WHERE  DATE_FORMAT(create_time,'%Y-%m-%d') = '2023-09-24';
            Integer newUserCount = this.orderMapper.countUserByParams(paramsMap);

            //SELECT COUNT(*) FROM `user` WHERE  DATE_FORMAT(create_time,'%Y-%m-%d') <= '2023-09-24';
            paramsMap.put("newUserFlag",false);
            Integer totalUserCount = this.orderMapper.countUserByParams(paramsMap);

            newUserList.add(newUserCount);
            totalUserList.add(totalUserCount);
        }*/

        //方式二：按日期分组进行统计
        Map paramsMap = new HashMap();
        paramsMap.put("beginDate",begin);
        paramsMap.put("endDate",end);
        List<Map> newUserMapList =  this.orderMapper.countUserByParamsWithGroupby(paramsMap);
        Map<String, Integer> newUserMap = newUserMapList.stream().collect(Collectors.toMap(u -> (String) u.get("date"), u -> Integer.valueOf(u.get("num").toString())));

        paramsMap.put("date",begin.minusDays(1));
        paramsMap.put("newUserFlag",false);
        Integer totalUserCount = this.orderMapper.countUserByParams(paramsMap);

        for (LocalDate date : dateList) {
            //1、获取每日新增数
            Integer newUserCount = newUserMap.getOrDefault(date.toString(),0);

            //2、获取截止当天用户总数
            totalUserCount += newUserCount;

            //3、分别放入各自的集合中
            newUserList.add(newUserCount);
            totalUserList.add(totalUserCount);
        }

        //3、拼接出参的三个字符串，封装vo返回
        String dateListStr = dateList.stream().map(date -> date.toString()).collect(Collectors.joining(","));
        String newUserListStr = newUserList.stream().map(num -> num.toString()).collect(Collectors.joining(","));
        String totalUserListStr = totalUserList.stream().map(num -> num.toString()).collect(Collectors.joining(","));

        return UserReportVO.builder().dateList(dateListStr).newUserList(newUserListStr).totalUserList(totalUserListStr).build();
    }

    /**
     * 订单统计
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return
     */
    @Override
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        //1、根据起始日期查询范围内的所有【日期集合】
        List<LocalDate> dateList = this.getDateList(begin, end);

        //2、根据起始日期分组查询日期范围内的【订单数】
        Map paramsMap = new HashMap();
        paramsMap.put("beginDate",begin);
        paramsMap.put("endDate",end);
        List<Map> orderCountMapList = this.orderMapper.orderCountByParamsWithGroupby(paramsMap);
        Map<String, Long> orderCountMap = orderCountMapList.stream().collect(Collectors.toMap(o -> (String) o.get("date"), o -> (Long) o.get("num")));

        //3、根据起始日期分组查询日期范围内的【有效订单数】
        paramsMap.put("status",Orders.COMPLETED);
        List<Map> validOrderCountMapList = this.orderMapper.orderCountByParamsWithGroupby(paramsMap);
        Map<String, Long> validOrderCountMap = validOrderCountMapList.stream().collect(Collectors.toMap(o -> (String) o.get("date"), o -> (Long) o.get("num")));

        //4、循环日期列表，组装【订单数集合】、【有效订单数集合】
        //4.1、定义两个集合
        List<Long> orderCountList = new ArrayList<>();
        List<Long> validOrderCountList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //4.2、从Map中获取对应日期的订单总数、有效订单数
            Long orderCount = orderCountMap.getOrDefault(date.toString(),Long.valueOf(0));
            Long validOrderCount = validOrderCountMap.getOrDefault(date.toString(), Long.valueOf(0));
            //4.3、将订单总数和有效订单数添加至 对应的集合中
            orderCountList.add(orderCount);
            validOrderCountList.add(validOrderCount);
        }

        //5、计算订单总数、有效订单数、订单完成率
        Long totalOrderCount = orderCountList.stream().mapToLong(o -> o).sum();
        totalOrderCount = orderCountList.stream().flatMapToLong(o -> LongStream.of(o)).sum();
        totalOrderCount = orderCountList.stream().reduce(Long::sum).get();

        Long validOrderCount = validOrderCountList.stream().mapToLong(o -> o).sum();
        Double orderCompletionRate = totalOrderCount == 0? 0.0 : validOrderCount.doubleValue() / totalOrderCount;

        //6、封装VO，返回
        return OrderReportVO.builder().dateList(dateList.stream().map(date -> date.toString()).collect(Collectors.joining(",")))
                                      .orderCountList(orderCountList.stream().map(c -> c.toString()).collect(Collectors.joining(",")))
                                      .validOrderCountList(validOrderCountList.stream().map(c -> c.toString()).collect(Collectors.joining(",")))
                                      .totalOrderCount(Integer.valueOf(totalOrderCount.toString()))
                                      .validOrderCount(Integer.valueOf(validOrderCount.toString()))
                                      .orderCompletionRate(orderCompletionRate)
                                      .build();
    }

    /**
     * 商品销量统计（前10）
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return
     */
    @Override
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        //1、统计查询指定日期范围内的菜品、套餐销售情况（菜品名称、销量）
        Map paramsMap = new HashMap();
        paramsMap.put("beginDate",begin);
        paramsMap.put("endDate",end);
        paramsMap.put("status",Orders.COMPLETED);
        List<GoodsSalesDTO> top10List = this.orderMapper.salesTop10ByParams(paramsMap);

        //2、组装数据，封装VO
        String nameListStr = top10List.stream().map(good -> good.getName()).collect(Collectors.joining(","));
        String numberListStr = top10List.stream().map(good -> good.getNumber().toString()).collect(Collectors.joining(","));
        return SalesTop10ReportVO.builder().nameList(nameListStr).numberList(numberListStr).build();
    }

    /**
     * 导出近30天运营数据
     *
     * @param response
     */
    @Override
    public void export(HttpServletResponse response) throws IOException, InvalidFormatException {
        //1、从数据库查询近30天的运营数据
        LocalDate beginDate = LocalDate.now().minusDays(30);
        LocalDate endDate = LocalDate.now().minusDays(1);
        BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(beginDate, LocalTime.MIN), LocalDateTime.of(endDate, LocalTime.MAX));

        //2、运用POI将数据写入excel
        //exportStyle1(response, beginDate, endDate, businessData);
        //exportStyle2(response, beginDate, endDate, businessData);
        exportStyle3(response, beginDate, endDate, businessData);
    }

    /**
     * 使用org.jxls进行基于excel模板的导出（模板根据批注读取配置）  poi  版本不能低于4.0
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @throws IOException
     */
    private void exportStyle3(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板3.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }
        businessData.setDate("时间：" + startDate + "至" + endDate);
        Context context = new Context();
        context.putVar("dataList" , voList);
        context.putVar("bd" , businessData);

        //5、写入到浏览器，关闭流
        response.setContentType("application/octet-stream");  ////非必须
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("运营数据报表1.xlsx", "UTF-8"));  //非必须
        JxlsHelper.getInstance().processTemplate(in, new BufferedOutputStream(response.getOutputStream()), context);
    }

    /**
     * 使用net.sf.jxls.jxls-core进行基于excel模板的导出(poi 版本不能高于4.0)
     *
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @return
     * @throws InvalidFormatException
     */
    private void exportStyle2(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws InvalidFormatException, IOException {
        //1、准备数据
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板2.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);

        //1.1、准备明细数据List
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }

        //使用net.sf.jxls.jxls-core进行基于excel模板的导出（模板根据表达式读取配置） poi 版本不能高于4.0
        Map<String, Object> map = new HashMap<>();
        map.put("dataList", voList);
        map.put("bd", businessData);

        Workbook excel = new XLSTransformer().transformXLS(in, map);
        excel.write(response.getOutputStream());
    }

    /**
     * 利用原生POI进行excel导出
     * @param response   响应对象
     * @param beginDate  开始日期
     * @param endDate     结束日期
     * @param businessData  日期范围内的运营数据
     * @throws IOException
     */
    private void exportStyle1(HttpServletResponse response, LocalDate beginDate, LocalDate endDate, BusinessDataVO businessData) throws IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(in);

        XSSFSheet sheet = workbook.getSheetAt(0);
        //设置日期
        sheet.getRow(1).getCell(1).setCellValue("时间：" + beginDate + "至" + endDate);
        //设置概览数据
        sheet.getRow(3).getCell(2).setCellValue(businessData.getTurnover());
        sheet.getRow(3).getCell(4).setCellValue(businessData.getOrderCompletionRate());
        sheet.getRow(3).getCell(6).setCellValue(businessData.getNewUsers());

        sheet.getRow(4).getCell(2).setCellValue(businessData.getValidOrderCount());
        sheet.getRow(4).getCell(4).setCellValue(businessData.getUnitPrice());

        //设置明细数据
        List<LocalDate> dateList = this.getDateList(beginDate, endDate);
        int rownum = 0;
        for (LocalDate date : dateList) {
            businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            XSSFRow row = sheet.getRow(7 + rownum++);
            row.getCell(1).setCellValue(date.toString());
            row.getCell(2).setCellValue(businessData.getTurnover());
            row.getCell(3).setCellValue(businessData.getValidOrderCount());
            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
            row.getCell(5).setCellValue(businessData.getUnitPrice());
            row.getCell(6).setCellValue(businessData.getNewUsers());
        }

        //3、将excel通过输出流下载至客户端浏览器
        workbook.write(response.getOutputStream());
        in.close();
        workbook.close();
    }

    /**
     * 根据起始日期获取之间的日期集合
     * @param begin
     * @param end
     * @return
     */
    private List<LocalDate> getDateList(LocalDate begin, LocalDate end) {
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (!begin.isEqual(end)){
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        return dateList;
    }
}
